/**
 * 
 */
package travibot.meta.dao.jdbctemplate;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;

import travibot.meta.MaterialType;
import travibot.meta.dao.MaterialTypeDAO;
import travibot.utility.DescriptionInstantiationException;
import travibot.utility.DescriptionNotFoundException;
import travibot.utility.LocaleSelector;


/**
 * @author tajzivit
 *
 */
public class MaterialTypeDAOJdbcTemplate implements MaterialTypeDAO {

    private static final String TABLE_TYPE = "MATERIAL_TYPE";

    private static final String TABLE_NAME = "MATERIAL_NAME";

    private static final String TABLE_TEXT = "TEXT";

    private JdbcTemplate jdbcTemplate;

    private LocaleSelector localeSelector;

    public void setDataSource(DataSource dataSource) {

        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    
    public void setLocaleSelector(LocaleSelector localeSelector) {

        this.localeSelector = localeSelector;
    }

    private static class MaterialTypeRowMapper implements RowMapper {

        private static final String ID = "ID_TYPE";
        private static final String NAME = "text";
        private static final String CLASS = "class";

        public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {

            MaterialType type = new MaterialType();
            type.setId((Integer)rs.getObject(ID));
            type.setName(rs.getString(NAME));
            try {
                type.setImplementingClass(Class.forName(rs.getString(CLASS)));
            }
            catch (ClassNotFoundException e) {
               throw new DescriptionInstantiationException(e);
            }
            return type;
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see travibot.meta.dao.BuildingTypeDAO#getBuildingType(java.lang.String)
     */
    public MaterialType getMaterialType(String name) throws DescriptionNotFoundException {

        MaterialType result;
        RowMapper mapper = new MaterialTypeRowMapper();
        try {
            String locale = localeSelector.getLocale().toString();
            StringBuffer sb = new StringBuffer();
            sb.append("select type.id_type, TEXT.text, type.class from ");
            sb.append("MATERIAL_NAME as material ");
            sb.append(" inner join " + TABLE_TYPE + " as type ON material.ID_TYPE = type.ID_TYPE ");
            sb.append(" inner join LOCALE ON material.ID_LOCALE = LOCALE.ID_LOCALE ");
            sb.append(" inner join TEXT ON material.ID_TEXT = TEXT.ID_TEXT ");
            sb.append("where (LOCALE.ID_LOCALE = ? and TEXT.text = ?)");
            result = (MaterialType)jdbcTemplate.queryForObject(sb.toString(), new Object[] { locale, name }, mapper);
        }
        catch (EmptyResultDataAccessException e) {
            throw new DescriptionNotFoundException(e);
        }
        return result;
    }

    /*
     * (non-Javadoc)
     * 
     * @see travibot.meta.dao.BuildingTypeDAO#setBuildingType(travibot.meta.BuildingType)
     */
    public void insertMaterialType(MaterialType type) {

        String sql = "insert into " + TABLE_TYPE + " (class) values (?)";
        jdbcTemplate.update(sql, new Object[] { type.getImplementingClassName()});
        sql = "select max(id_type) from " + TABLE_TYPE;
        type.setId(jdbcTemplate.queryForInt(sql));

        sql = "insert into " + TABLE_TEXT + " (text) values (?)";
        jdbcTemplate.update(sql, new Object[] { type.getName() });
        sql = "select max(id_text) from " + TABLE_TEXT;
        int idText = jdbcTemplate.queryForInt(sql);

        sql = "insert into " + TABLE_NAME + " (ID_TEXT, ID_LOCALE, ID_TYPE) values (?, ?, ?)";
        jdbcTemplate.update(sql, new Object[] { idText, localeSelector.getLocale().toString(), type.getId() });

    }

    public void deleteMaterialType(MaterialType type) {

        // TODO Auto-generated method stub
        
    }

    public List<MaterialType> getMaterialTypes() {

        RowMapper mapper = new MaterialTypeRowMapper();
        String locale = localeSelector.getLocale().toString();
        StringBuffer sb = new StringBuffer();
        sb.append("select type.id_type, TEXT.text, type.class from ");
        sb.append("MATERIAL_NAME as material ");
        sb.append(" inner join " + TABLE_TYPE + " as type ON material.ID_TYPE = type.ID_TYPE ");
        sb.append(" inner join LOCALE ON material.ID_LOCALE = LOCALE.ID_LOCALE ");
        sb.append(" inner join TEXT ON material.ID_TEXT = TEXT.ID_TEXT ");
        sb.append("where LOCALE.ID_LOCALE = ?");
        List result = (List)jdbcTemplate.query(sb.toString(), new Object[] {locale},
                                               new RowMapperResultSetExtractor(mapper));
        return new ArrayList<MaterialType>(result);
    }

    public MaterialType getMaterialType(int id) throws DescriptionNotFoundException {

        MaterialType result;
        RowMapper mapper = new MaterialTypeRowMapper();
        try {
            String locale = localeSelector.getLocale().toString();
            StringBuffer sb = new StringBuffer();
            sb.append("select type.id_type, TEXT.text, type.class from ");
            sb.append("MATERIAL_NAME as material ");
            sb.append(" inner join " + TABLE_TYPE + " as type ON material.ID_TYPE = type.ID_TYPE ");
            sb.append(" inner join LOCALE ON material.ID_LOCALE = LOCALE.ID_LOCALE ");
            sb.append(" inner join TEXT ON material.ID_TEXT = TEXT.ID_TEXT ");
            sb.append("where (LOCALE.ID_LOCALE = ? and type.id_type = ?)");
            result = (MaterialType)jdbcTemplate.queryForObject(sb.toString(), new Object[] { locale, id }, mapper);
        }
        catch (EmptyResultDataAccessException e) {
            throw new DescriptionNotFoundException(e);
        }
        return result;
    }
}
